package com.davidhigenbottam.HeatMap.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;

import com.davidhigenbottam.HeatMap.shared.Institution;
import com.davidhigenbottam.HeatMap.shared.Query;


public class HigherEducationEnrolementInstitutionData extends InstitutionData {

	
	public HigherEducationEnrolementInstitutionData(Query query) {
		super(query);
		getInstitutionsFromDatabase();
	
	}


	public void getInstitutionsFromDatabase() {
		Connection c = null;
		try {
			c = getConnection();
			

			Boolean filterLevelEducation= !query.getLevelOfEducation().equals(Query.STUDY_LEVEL_ALL);
			String levelEducationClause="";
			if(filterLevelEducation){
				//filter on level of education
				levelEducationClause = "and sg.study_id=? ";
			}			
			
			Boolean filterInstType= !query.getInstitutionType().equals(Query.INSTITUTE_TYPE_ALL);
			String institutionTypeClause="";
			if(filterInstType){
				//filter on institute type
				institutionTypeClause = "and sg.inst_form_id=? ";
			}
			
			Boolean filterInstOwnership= !query.getInstituteOwnership().equals(Query.INSTITUTE_OWNERSHIP_ALL);
			String instituteOwnershipClause="";
			if(filterInstOwnership){
				//filter on institute type
				instituteOwnershipClause = "and sg.type_inst=? ";
			}
			
			
			String statement = "select sg.inst_name, gi.acronym, gi.url, gi.geocode1, gi.private , sg.sumtotal  "
					+ "from (select inst_id, inst_name, sum(total) as sumtotal " +
					"		 from student_group " +
					"		 where year=? " 
					+ levelEducationClause
					+ institutionTypeClause
					+ instituteOwnershipClause +
					"		 group by inst_id) as sg " 
					+ "LEFT JOIN geocoded_institution as gi " +
					"ON sg.inst_id = gi.institution_id " +
					"order by sumtotal desc " +
					"LIMIT "+Institution.NUMBER_OF_ROWS;
					
			
			
			
			
			log.info("statement:"+statement);
			
			PreparedStatement stmt = c.prepareStatement(statement);
			int idx=1;
			stmt.setString(idx++, query.getYear());
			if(filterLevelEducation)
				stmt.setString(idx++, query.getLevelOfEducation());
			if(filterInstType)
				stmt.setString(idx++, query.getInstitutionType());
			if(filterInstOwnership)
				stmt.setString(idx++, query.getInstituteOwnership());
			
			if (stmt.execute()) {
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					String name = rs.getString("inst_name");
					String acronym = rs.getString("acronym");
					String url = rs.getString("url");
					String geocode= rs.getString("geocode1");
					String privateInst= rs.getString("private");
					
					Long sumTotal = rs.getLong("sumtotal");
					
					Institution i = new Institution( name, acronym ,url, geocode, privateInst, Double.valueOf(sumTotal.doubleValue()));
			
					addInstitution(i);
				}
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "SQLException", e);
			throw new RuntimeException(e);
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
		}
		
	}




	

	

}
